using System.Data;
using MySqlConnector;

namespace Front._1118;

public partial class BookForm : Form
{
   
    public BookForm()
    {
        InitializeComponent();
    }
    
    
    
    private void BookForm_Load(object sender, EventArgs e)
    {
        GetAllBooks();
        GetAllCategories();
    }

    // 修改GetAllBooks方法，添加查询参数
    private void GetAllBooks(string bookName = "", string category = "全部")
        {
            try
            {
                // 1. 数据库连接字符串
                string connStr = "server=localhost;port=3306;database=book;user=root;password=123456;charset=utf8mb4;AllowPublicKeyRetrieval=True;";
                
                // 2. 构建SQL查询语句
                // 使用 WHERE 1=1 方便后续拼接条件
                string sql = "SELECT book_id, isbn, book_name, author, book_category FROM book_info WHERE 1=1";

                using (MySqlConnection connection = new MySqlConnection(connStr))
                {
                    connection.Open();

                    using (MySqlCommand command = new MySqlCommand(sql, connection))
                    {
                        // 3. 根据传入的参数，动态添加查询条件
                        // 书名模糊查询
                        if (!string.IsNullOrEmpty(bookName))
                        {
                            sql += " AND book_name LIKE @bookName";
                            command.Parameters.AddWithValue("@bookName", "%" + bookName + "%");
                        }

                        // 分类精确查询（排除"全部"选项）
                        if (!string.IsNullOrEmpty(category) && category != "全部")
                        {
                            sql += " AND book_category = @category";
                            command.Parameters.AddWithValue("@category", category);
                        }

                        // 4. 执行查询并填充数据到DataTable
                        command.CommandText = sql;
                        MySqlDataAdapter adapter = new MySqlDataAdapter(command);
                        DataTable dataTable = new DataTable();
                        adapter.Fill(dataTable);

                        // 5. 将数据绑定到DataGridView
                        // 重要：确保AutoGenerateColumns为false，这样才会使用你在设计器中定义的列
                        dataGridView1.AutoGenerateColumns = false;
                        dataGridView1.DataSource = dataTable;
                    }
                }
            }
            catch (Exception ex)
            {
                // 捕获并显示任何异常
                MessageBox.Show("加载图书数据失败：" + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
        
    

    private void GetAllCategories()
    {
        try
        {
            string connStr = "server=localhost;port=3306;database=book;user=root;password=123456;charset=utf8mb4;AllowPublicKeyRetrieval=True;";
            string sql = "SELECT DISTINCT book_category FROM book_info";

            using (MySqlConnection connection = new MySqlConnection(connStr))
            {
                connection.Open();
                using (MySqlCommand command = new MySqlCommand(sql, connection))
                {
                    MySqlDataReader reader = command.ExecuteReader();

                    // 先清空下拉框，并添加"全部"选项
                    comboBox1.Items.Clear();
                    comboBox1.Items.Add("全部");

                    // 读取数据库中的分类并添加到下拉框
                    while (reader.Read())
                    {
                        string categoryName = reader["book_category"].ToString();
                        if (!string.IsNullOrEmpty(categoryName))
                        {
                            comboBox1.Items.Add(categoryName);
                        }
                    }

                    // 设置默认选中项为"全部"
                    comboBox1.SelectedIndex = 0;
                }
            }
        }
        catch (Exception ex)
        {
            MessageBox.Show("加载图书分类失败：" + ex.Message, "错误", MessageBoxButtons.OK, MessageBoxIcon.Error);
        }
    }
    
    
    private void button1_Click(object sender, EventArgs e)
    {
        // 获取查询条件
        string bookName = textBox1.Text.Trim();
        string category = comboBox1.SelectedItem?.ToString() ?? "全部";
    
        // 执行查询
        GetAllBooks(bookName, category);
    }
    

    private void dataGridView1_CellContentClick(object sender, DataGridViewCellEventArgs e)
    {
        throw new System.NotImplementedException();
    }

    private void button2_Click(object sender, EventArgs e)
    {
        // 打开新增窗体
        BookAdd bookAddForm = new BookAdd();
        bookAddForm.ShowDialog(); // 模态显示，关闭后可刷新列表
        // 新增成功后刷新图书列表
        GetAllBooks();
    }

    private void 删除ToolStripMenuItem_Click(object sender, EventArgs e)
    {
        DialogResult result=MessageBox.Show(text: "确定删除此数据", caption: "提示", MessageBoxButtons.OKCancel);
        if (result == DialogResult.OK)
        {
            int bookId = (int)this.dataGridView1.SelectedRows[0].Cells[0].Value;
            using var connection = new MySqlConnection("server=localhost;port=3306;database=book;user=root;password=123456;charset=utf8mb4;AllowPublicKeyRetrieval=True;");
            connection.Open();
            
            string sql="delete FROM book_info where bookId= @bookId";
            using var command = new MySqlCommand(sql, connection);
            
            command.Parameters.AddWithValue("bookId", bookId);
            int count = command.ExecuteNonQuery();
            if (count > 0)
            {
                MessageBox.Show(text: "数据删除成功");
            }
        }
    }
} 
   